
CREATE DATABASE HawaiiRestaurant

USE HawaiiRestaurant
GO

CREATE TABLE DrinkType
(
	DrinkTypeId int identity(1,1) primary key,
	Name nvarchar(100),
	Descriptions nvarchar(500) null
)

CREATE TABLE Drink
(
	DrinkId int identity(1,1) primary key,
	Name nvarchar(100),
	Descriptions nvarchar(500) null,
	[Image] nvarchar(200) null,
	DrinkTypeId int constraint fk_DrinkTypeId foreign key references DrinkType(DrinkTypeId),
	DrinkUnit nvarchar(200) null,
	ModifiedDate datetime default getdate(),
	Favorite int default 0,
	StandardPrice decimal(20,3) default 0
)

CREATE TABLE FoodType
(
	FoodTypeId int identity(1,1) primary key,
	Name nvarchar(100),
	Descriptions nvarchar(500) null
)

CREATE TABLE Food
(
	FoodId int identity(1,1) primary key,
	Name nvarchar(100),
	[Image] nvarchar(200) null,
	Descriptions nvarchar(500) null,
	FoodTypeId int constraint fk_FoodTypeId foreign key references FoodType(FoodTypeId),
	FoodUnit nvarchar(200) null,
	ModifiedDate datetime default getdate(),
	Favorite int default 0,
	StandardPrice decimal(20,3) default 0
)

CREATE TABLE Position
(
	PositionId int identity(1,1) primary key,
	Name nvarchar(100)
)

CREATE TABLE [User]
(
	UserName varchar(20) primary key,
	[Password] varchar(20),
	IsActive bit default 1
)

CREATE TABLE Customer
(
	CustomerId int identity(1,1) primary key,
	Name nvarchar(100),
	CustomerAddress nvarchar(200) null,
	CustomePhone varchar(20) null,
	CustomerFax varchar(50) null,
	CustomerAccount varchar(50) null,
	PositionId int constraint fk_PositionId foreign key references Position(PositionId),
	OrganizationName nvarchar(200) null,
	OrganizationAddress nvarchar(200) null,
	UserName varchar(20) constraint fk_UserName_Customer foreign key references [User](UserName),
)

CREATE TABLE Staff
(
	StaffId int identity(1,1) primary key,
	Name nvarchar(100),
	PhoneNumber varchar(20) null,
	[Address] nvarchar(500) null,
	IsAdmin bit default 0,
	UserName varchar(20) constraint fk_UserName_Staff foreign key references [User](UserName),
)

CREATE TABLE [Contract]
(
	ContractId int identity(1,1) primary key,
	StaffId int constraint fk_PersonId_StaffId foreign key references Staff(StaffId),
	CustomerId int constraint fk_PersonId_CustomerId foreign key references Customer(CustomerId),
	ContractDate datetime,
	PartyDate datetime,
	PartyAddress nvarchar(200) null,
	PartyType nvarchar(200) null,
	Promotion nvarchar(200) null,
	PartyConsultancy nvarchar(200) null,
	PartySetupStyle nvarchar(200) null,
	PaymentMethod nvarchar(200) null,
	TransportationVehicle nvarchar(200) null,
	Discount decimal(20,3),
	OlderAmount int,
	ChildAmount int,
	CustomerAmountPerTable int,
	OtherRequirements nvarchar(max) null,
	CultureAndArt nvarchar(500) null,
	ContractTotalAmount decimal(20,3),
	TotalDeposit decimal(20,3)
)

CREATE TABLE Contract_Food
(
	ContractId int constraint fk_ContractId_Contract_Contract_FoodId foreign key references [Contract](ContractId),
	FoodId int constraint fk_ItemId_Contract_Item foreign key references Food(FoodId),
	Quantity int,
	Price decimal(20,3),
	primary key(ContractId,FoodId)
)

CREATE TABLE Contract_Drink
(
	ContractId int constraint fk_ContractId_Contract_Contract_DrinkId foreign key references [Contract](ContractId),
	DrinkId int constraint fk_ItemId_Contract_DrinkId foreign key references Drink(DrinkId),
	Quantity int,
	Price decimal(20,3),
	primary key(ContractId,DrinkId)
)

CREATE TABLE Receipt
(
	ReceiptId int identity(1,1) primary key,
	ReceiptDate datetime,
	ContractId int constraint fk_ContractId_Receipt foreign key references [Contract](ContractId),
	StaffId int constraint fk_PersonId_StaffId_Receipt foreign key references Staff(StaffId),
	CustomerId int constraint fk_PersonId_CustomerId_Receipt foreign key references Customer(CustomerId)
)

CREATE TABLE LookupItemType
(
	LookupItemTypeId int identity(1,1) primary key,
	Name nvarchar(200)
)

CREATE TABLE LookupItem
(
	LookupItemId int identity(1,1) primary key,
	Name nvarchar(200),
	[Image] nvarchar(200) default 'Image.jpeg',
	LookupItemTypeId int constraint fk_LookupItemTypeId foreign key references LookupItemType(LookupItemTypeId),
	IsActive bit
)